********************************************************************************
** 	TITLE:		UK10_results                                                  ** 
**	AUTHOR:	    Philippe Mongrain                                             **
**	DATA:       1918-2019election_results_by_pcon                             **
**	VERSION:	Stata 16					                                  **
**	DATE:		October 2022  				                                  **
********************************************************************************

* Version control

version 16.0

* Open log file

capture log close
log using "UK10_results.smcl", replace

* Import the dataset

import excel "1918-2019election_results_by_pcon.xlsx", cellrange(B5:AW654) sheet(2010) clear

* Reshape

drop H K N Q T W Z AC AF AI AL AO AR AU

rename B ons_id
rename C constituency_name
rename D county
rename E region
rename F country
rename G electorate
rename I votes1
rename J share1
rename L votes2
rename M share2
rename O votes3
rename P share3
rename R votes4
rename S share4
rename U votes5
rename V share5
rename X votes6
rename Y share6
rename AA votes7
rename AB share7
rename AD votes8
rename AE share8
rename AG votes9
rename AH share9
rename AJ votes10
rename AK share10
rename AM votes11
rename AN share11
rename AP votes12
rename AQ share12
rename AS votes13
rename AT share13
rename AV totalvotes
rename AW turnout

reshape long votes share, i(ons_id) j(party_code)  

gen party_name = " "

replace party_name = "Conservative" if party_code == 1 
replace party_name = "Liberal Democrat" if party_code == 2
replace party_name = "Labour" if party_code == 3
replace party_name = "UK Independence Party" if party_code == 4
replace party_name = "Green" if party_code == 5 
replace party_name = "SNP" if party_code == 6
replace party_name = "Plaid Cymru" if party_code == 7
replace party_name = "DUP" if party_code == 8
replace party_name = "Sinn Fein" if party_code == 9
replace party_name = "SDLP" if party_code == 10
replace party_name = "UUP" if party_code == 11
replace party_name = "Alliance" if party_code == 12
replace party_name = "Other" if party_code == 13

drop party_code

gen party_code = .

replace party_code = 1 if party_name == "Conservative"
replace party_code = 2 if party_name == "Labour"
replace party_code = 3 if party_name == "Liberal Democrat"
replace party_code = 4 if party_name == "SNP"
replace party_code = 5 if party_name == "Plaid Cymru"
replace party_code = 6 if party_name == "UK Independence Party"
replace party_code = 7 if party_name == "Green"
replace party_code = -9999 if country == "Northern Ireland"
replace party_code = 9 if party_code > 7
 
* Generate rank of parties

gsort constituency_name -votes
bysort constituency_name : gen rank = _n

gen first = party_name if rank == 1
gen second = party_name if rank == 2
gen third = party_name if rank == 3

bysort constituency_name : gen winner10 = first[1]
bysort constituency_name : gen runnerup10 = second[2]
bysort constituency_name : gen thirdplace10 = third[3]

* Generate margins of victory

bysort constituency_name : gen margin = (share[1] - share[2])*100

* Generate effective number of electoral parties

gen sqrvotepr = (share)^2

bysort constituency_name : egen sumsqrvotepr = sum(sqrvotepr)

gen enep = 1/sumsqrvotepr

* Labelling parties

replace party_name = "1" if party_name == "Conservative"
replace party_name = "2" if party_name == "Labour"
replace party_name = "3" if party_name == "Liberal Democrat"
replace party_name = "4" if party_name == "Scottish National Party"
replace party_name = "5" if party_name == "Plaid Cymru"
replace party_name = "6" if party_name == "UK Independence Party"
replace party_name = "7" if party_name == "Green"
replace party_name = "9" if party_name!="1" & party_name!="2" & party_name!="3" & party_name!="4" & party_name!="5" & party_name!="6" & party_name!="7"

destring (party_name), replace

replace winner10 = "1" if winner10 == "Conservative"
replace winner10 = "2" if winner10 == "Labour"
replace winner10 = "3" if winner10 == "Liberal Democrat"
replace winner10 = "4" if winner10 == "Scottish National Party"
replace winner10 = "5" if winner10 == "Plaid Cymru"
replace winner10 = "6" if winner10 == "UK Independence Party"
replace winner10 = "7" if winner10 == "Green"
replace winner10 = "9" if winner10!="1" & winner10!="2" & winner10!="3" & winner10!="4" & winner10!="5" & winner10!="6" & winner10!="7"

destring (winner10), replace

replace runnerup10 = "1" if runnerup10 == "Conservative"
replace runnerup10 = "2" if runnerup10 == "Labour"
replace runnerup10 = "3" if runnerup10 == "Liberal Democrat"
replace runnerup10 = "4" if runnerup10 == "Scottish National Party"
replace runnerup10 = "5" if runnerup10 == "Plaid Cymru"
replace runnerup10 = "6" if runnerup10 == "UK Independence Party"
replace runnerup10 = "7" if runnerup10 == "Green"
replace runnerup10 = "9" if runnerup10!="1" & runnerup10!="2" & runnerup10!="3" & runnerup10!="4" & runnerup10!="5" & runnerup10!="6" & runnerup10!="7"

destring (runnerup10), replace

replace thirdplace10 = "1" if thirdplace10 == "Conservative"
replace thirdplace10 = "2" if thirdplace10 == "Labour"
replace thirdplace10 = "3" if thirdplace10 == "Liberal Democrat"
replace thirdplace10 = "4" if thirdplace10 == "Scottish National Party"
replace thirdplace10 = "5" if thirdplace10 == "Plaid Cymru"
replace thirdplace10 = "6" if thirdplace10 == "UK Independence Party"
replace thirdplace10 = "7" if thirdplace10 == "Green"
replace thirdplace10 = "9" if thirdplace10!="1" & thirdplace10!="2" & thirdplace10!="3" & thirdplace10!="4" & thirdplace10!="5" & thirdplace10!="6" & thirdplace10!="7"

destring (thirdplace10), replace

label define party 1 "Conservative" 2 "Labour" 3 "Liberal Democrat" 4 "SNP" 5 "Plaid Cymru" 6 "UK Independence Party" 7 "Green" 9 "Other"
label values party_name winner10 runnerup10 thirdplace10 party
 
* Selecting, ordering, and sorting variables

keep ons_id constituency_name party_name rank votes share margin enep winner10 runnerup10 thirdplace10

order ons_id constituency_name party_name rank votes share margin enep winner10 runnerup10 thirdplace10

sort ons_id rank

drop if rank > 3

* Drop duplicates

duplicates tag ons_id, gen(dup)
duplicates drop ons_id, force
drop dup

* Change constituency ID

replace ons_id = subinstr(ons_id,"E1400","1",.)
replace ons_id = subinstr(ons_id,"S1400","2",.)
replace ons_id = subinstr(ons_id,"W0700","3",.)
replace ons_id = subinstr(ons_id,"N0600","4",.)

* Create election variable

gen election10 = "2010 British general election"

* Saving file

save UK10_results, replace

log close